clear all

* Set your directory path here
local root "D:\Understanding_Bank_Payouts"

capture log close
log using "`root'\Code\Logs\Final_code_RCFS\Table1.log", replace
display "$S_TIME  $S_DATE"

set more off
set varabbrev off, perm

local panelfile "`root'\Data\Final\SNL_with_ITR_w_actualTARPdates.dta" 
local finalfile_quarterly "`root'\Data\Final\Finalfile_quarterly.dta" 
local finalfile_yearly "`root'\Data\Final\Finalfile_yearly.dta" 

capture noisily mkdir "`root'\Results"
capture noisily mkdir "`root'\Results\Final_code_RCFS"
cd "`root'\Results\Final_code_RCFS"

use `finalfile_yearly', clear
keep if avg_divyieldWY != .

**** Table 1, Panel A descriptives

file open Table1A using "Table1A.txt", write replace

file write Table1A "Yearly break-down of the number of observations with non-missing dividend data" _n(2)
file write Table1A _tab "Banks with non-missing dividend data" _tab ///
	"Banks with non-missing data on repurchases" _n

forvalues y = 1995/2012 {
	file write Table1A "`y'" _tab 

	sum avg_divyieldY if year == `y'
	file write Table1A (r(N)) _tab

	sum reppshY1 if year == `y'
	file write Table1A (r(N)) _n
	
	}

file close Table1A

**** Table 1, Panel B descriptives

use `finalfile_yearly', clear
keep if avg_divyieldWY != .
merge m:1 instkey using "`root'\Data\RCFS conditional acceptance\Distance_to_default_2006.dta"
drop _merge
merge m:1 permno using "`root'\Data\RCFS conditional acceptance\Crisisreturns_daysincrisis1998.dta"
drop _merge
merge m:1 cusip6 using "`root'\Data\RCFS conditional acceptance\Crisisreturns_daysincrisis1998.dta", update
drop _merge
merge 1:1 cusip6 year using "`root'\Data\RCFS conditional acceptance\Institutional_ownership_types_detailed.dta", update
drop if _merge == 2
drop _merge

generate avg_logassets_sqY = avg_logassetsY*avg_logassetsY
regress avg_logassets_sqY avg_logassetsY
predict sizesq_orth, residuals
label variable avg_logassetsY "Size (log assets)"
label variable sizesq_orth "Size (log assets) squared, orthogonalized"

rename avg_divdivbookvalueY divbookY
rename divpayoutratioYALT divpayoutY
rename avg_divpayoutratioY divpayoutY_SNL
rename divpershare_year divpershY

generate avg_tassetsW_BN = avg_totassetsWY/(10^9)
generate avg_tassetsW_BN_sq = avg_tassetsW_BN * avg_tassetsW_BN

xtset instkey year

generate HMDA_to_total3 = (loanvalue + L.loanvalue + L2.loanvalue)/((gross_lns + L.gross_lns + L2.gross_lns)/3)
generate HMDA_to_total2 = (loanvalue + L.loanvalue)/((gross_lns + L.gross_lns)/2)

generate lenientV3 = (loanvalue + L.loanvalue + L2.loanvalue) / (appvalue + L.appvalue + L2.appvalue)
generate lenientC3 = (loancount + L.loancount + L2.loancount) / (appcount + L.appcount + L2.appcount)

generate lenientV2 = (loanvalue + L.loanvalue) / (appvalue + L.appvalue)
generate lenientC2 = (loancount + L.loancount) / (appcount + L.appcount)

foreach var of varlist HMDA_to_total3 lenientV3 lenientC3 {
	generate chg_`var' = D3.`var'
	}

foreach var of varlist HMDA_to_total2 lenientV2 lenientC2 {
	generate chg_`var' = D2.`var'
	}

// Make a new variable that captures analyst coverage, including zeroes

generate numest_zeroes = numest
replace numest_zeroes = 0 if numest == .

// Analyst dummy

generate analyst_dummy = 0
replace analyst_dummy = 1 if numest != .

// Generate lag variables to line these up with the 2004-2006 period (since we sort on year 2008 in the quantiles)

xtset instkey year

generate L2HMDA_to_total3 = L2.HMDA_to_total3 
generate L2lenientV3 = L2.lenientV3

foreach var in fedfunds_balance {
	winsor `var', generate(`var'W) p(0.01)
	label variable `var'W "`var' winsorized at the 1%"
	}

foreach var in fedfunds_balance fedfunds_balanceW {
	by instkey year: egen avg_`var'Y = mean(`var')
	by instkey year: gen `var'4 = `var'[4]  	// The 4th quarter value of the variable in each year
	}

generate avg_fedfunds_repos_scaledWY = (avg_reposWY + avg_fedfunds_balanceWY)/avg_totassetsWY
winsor avg_fedfunds_repos_scaledWY, generate(helper) p(0.01)
drop avg_fedfunds_repos_scaledWY
rename helper avg_fedfunds_repos_scaledWY
label variable avg_fedfunds_repos_scaledWY "Total Fed funds + repos scaled by total assets"

generate at_million = avg_totassetsY/(10^6)

local rhs6 L.avg_logassetsY L.sizesq_orth  ///
L.avg_mtbWY L.avg_roaaWY L.lev_fps L.reteY growth2WY L.avg_tieroneratioWY

local y divpshY1W

drop if neverpayer == 1

quietly reghdfe `y' `rhs6' if year < 2007, absorb(instkey year) vce(cluster instkey) residuals
generate t2sample = e(sample)
tab t2sample

xtreg `y' `rhs6' i.year if year < 2009, fe vce(cluster instkey) 
generate t3sample = e(sample)
tab t3sample

local x divgrowthY1
generate year_2007 = (year == 2007)
generate year_2008 = (year == 2008)
quietly regress F.returnY `x' c.`x'#c.year_2007 c.`x'#c.year_2008 year_2007 year_2008 `rhs6' , cluster(instkey)
generate t6sample = e(sample)
tab t6sample

drop if t2sample == 0 & t2sample == 0 & t6sample == 0

foreach var in divdum1 divpshY1W avg_divyieldWY divbookY ///
	divgrowthY1_zero repdum1 tpodum1 tpopshY1 avg_tpoyieldYW at_million avg_logassetsY /// 
	sizesq_orth avg_mtbWY lev_fps reteY growth2WY avg_tieroneratioWY ///
	avg_stratioY earningsvol lenientV3 tarpever disttodef2006 ///
	avg_int_to_nonintWY pctown_insidersSNL_Y instown_perc x_npr_countY numest_zeroes crisis_return98 ///
	ded_own_perc qix_own_perc tra_own_perc nr_inst_blk_owners {

		replace `var' = . if (t2sample == 0 & t3sample == 0)

		}

foreach var of varlist divpshY1W avg_divyieldWY divbookY ///
	divgrowthY1_zero  tpopshY1 avg_tpoyieldYW  ///
	divpshY1 tpopshY1 avg_tieroneratioWY ded_own_perc qix_own_perc tra_own_perc pctown_insidersSNL_Y instown_perc x_npr_countY {
	
	winsor `var', generate(helper) p(0.01)
	drop `var'
	rename helper `var'
	}

// Rescale insider ownership by 100
generate a = pctown_insidersSNL_Y/100
drop pctown_insidersSNL_Y
rename a pctown_insidersSNL_Y

// Check that institutional ownership from the various sources is similar
gen helper = ded_own_perc + qix_own_perc + tra_own_perc
binscatter instown_perc helper

drop if year > 2009

estpost summarize divpshY1W avg_divyieldWY divbookY ///
	divgrowthY1_zero  tpopshY1 avg_tpoyieldYW at_million  /// 
	avg_mtbWY avg_roaaWY lev_fps reteY growth2WY avg_tieroneratioWY ///
	avg_stratioY earningsvol lenientV3 tarpever disttodef2006 ///
	avg_int_to_nonintWY instown_perc numest_zeroes  ///
	ded_own_perc qix_own_perc tra_own_perc nr_inst_blk_owners pctown_insidersSNL_Y x_npr_countY,  detail
esttab . using "Table1B", ///
cells("mean p50 sd min max count") noobs noisily csv replace 


display "$S_TIME  $S_DATE"
capture log close
clear all


